import { mysqlTable, mysqlSchema, AnyMySqlColumn, primaryKey, varchar, datetime, foreignKey, unique, index, decimal, int } from "drizzle-orm/mysql-core"
import { sql } from "drizzle-orm"

export const accountGroup = mysqlTable("account_group", {
	id: varchar({ length: 255 }).notNull(),
	title: varchar({ length: 50 }).notNull(),
	dateCreated: datetime("date_created", { mode: 'string', fsp: 6 }).notNull(),
	lastUpdated: datetime("last_updated", { mode: 'string', fsp: 6 }).notNull(),
},
(table) => [
	primaryKey({ columns: [table.id], name: "account_group_id"}),
]);

export const accountGroupUser = mysqlTable("account_group_user", {
	id: varchar({ length: 255 }).notNull(),
	// Warning: Can't parse bit(1) from database
	// bit(1)Type: bit(1)("is_account_group_creator").notNull(),
	userId: varchar("user_id", { length: 255 }).notNull().references(() => user.id, { onDelete: "restrict", onUpdate: "restrict" } ),
	accountGroupId: varchar("account_group_id", { length: 255 }).notNull().references(() => accountGroup.id, { onDelete: "restrict", onUpdate: "restrict" } ),
},
(table) => [
	primaryKey({ columns: [table.id], name: "account_group_user_id"}),
	unique("UK49431f70e0388222b0953652060e").on(table.accountGroupId, table.userId),
]);

export const appErrorReport = mysqlTable("app_error_report", {
	id: varchar({ length: 255 }).notNull(),
	systemInfo: varchar("system_info", { length: 1000 }).notNull(),
	dateCreated: datetime("date_created", { mode: 'string', fsp: 6 }).notNull(),
	stackTrace: varchar("stack_trace", { length: 4000 }).notNull(),
	username: varchar({ length: 255 }),
	errorMessage: varchar("error_message", { length: 1000 }).notNull(),
	deviceInfo: varchar("device_info", { length: 1000 }),
},
(table) => [
	primaryKey({ columns: [table.id], name: "app_error_report_id"}),
]);

export const appReleaseLog = mysqlTable("app_release_log", {
	id: varchar({ length: 255 }).notNull(),
	changeLog: varchar("change_log", { length: 255 }).notNull(),
	apkFilename: varchar("apk_filename", { length: 255 }).notNull(),
	appVersion: varchar("app_version", { length: 255 }).notNull(),
	releaseDate: datetime("release_date", { mode: 'string', fsp: 6 }).notNull(),
	appStoreUrl: varchar("app_store_url", { length: 255 }).notNull(),
	// Warning: Can't parse bit(1) from database
	// bit(1)Type: bit(1)("is_mandatory").notNull(),
},
(table) => [
	primaryKey({ columns: [table.id], name: "app_release_log_id"}),
]);

export const bank = mysqlTable("bank", {
	id: varchar({ length: 255 }).notNull(),
	shortName: varchar("short_name", { length: 50 }).notNull(),
	dateCreated: datetime("date_created", { mode: 'string', fsp: 6 }).notNull(),
	lastUpdated: datetime("last_updated", { mode: 'string', fsp: 6 }).notNull(),
	name: varchar({ length: 50 }).notNull(),
},
(table) => [
	primaryKey({ columns: [table.id], name: "bank_id"}),
]);

export const bankCard = mysqlTable("bank_card", {
	id: varchar({ length: 255 }).notNull(),
	dateCreated: datetime("date_created", { mode: 'string', fsp: 6 }).notNull(),
	statementDate: varchar("statement_date", { length: 50 }),
	lastUpdated: datetime("last_updated", { mode: 'string', fsp: 6 }).notNull(),
	name: varchar({ length: 50 }).notNull(),
	accountGroupId: varchar("account_group_id", { length: 255 }).notNull().references(() => accountGroup.id, { onDelete: "restrict", onUpdate: "restrict" } ),
	bankId: varchar("bank_id", { length: 255 }).notNull().references(() => bank.id, { onDelete: "restrict", onUpdate: "restrict" } ),
	paymentDueDate: varchar("payment_due_date", { length: 50 }),
	customerServiceNumber: varchar("customer_service_number", { length: 50 }),
	// Warning: Can't parse bit(1) from database
	// bit(1)Type: bit(1)("is_credit_card").notNull(),
	// Warning: Can't parse bit(1) from database
	// bit(1)Type: bit(1)("enabled").notNull(),
},
(table) => [
	primaryKey({ columns: [table.id], name: "bank_card_id"}),
]);

export const incomeExpenseDetail = mysqlTable("income_expense_detail", {
	id: varchar({ length: 255 }).notNull(),
	bankCardId: varchar("bank_card_id", { length: 255 }).references(() => bankCard.id, { onDelete: "restrict", onUpdate: "restrict" } ),
	detailType: varchar("detail_type", { length: 255 }).notNull(),
	dateCreated: datetime("date_created", { mode: 'string', fsp: 6 }).notNull(),
	paymentType: varchar("payment_type", { length: 255 }),
	lastUpdated: datetime("last_updated", { mode: 'string', fsp: 6 }).notNull(),
	updatedBy: varchar("updated_by", { length: 255 }).notNull(),
	details: varchar({ length: 100 }).notNull(),
	type: varchar({ length: 255 }).notNull(),
	accountGroupId: varchar("account_group_id", { length: 255 }).notNull().references(() => accountGroup.id, { onDelete: "restrict", onUpdate: "restrict" } ),
	createdBy: varchar("created_by", { length: 255 }).notNull(),
	incomeExpenseDate: datetime("income_expense_date", { mode: 'string', fsp: 6 }).notNull(),
	remark: varchar({ length: 100 }),
	amount: decimal({ precision: 19, scale: 2 }).notNull(),
	province: varchar({ length: 255 }),
	district: varchar({ length: 255 }),
	formattedAddress: varchar("formatted_address", { length: 255 }),
	latitude: decimal({ precision: 19, scale: 6 }),
	address: varchar({ length: 255 }),
	longitude: decimal({ precision: 19, scale: 6 }),
	city: varchar({ length: 255 }),
},
(table) => [
	index("idx_income_expense_detail_detail_type").on(table.detailType),
	index("idx_income_expense_detail_income_expense_date").on(table.incomeExpenseDate),
	primaryKey({ columns: [table.id], name: "income_expense_detail_id"}),
]);

export const incomeExpenseDetailBaseType = mysqlTable("income_expense_detail_base_type", {
	id: varchar({ length: 255 }).notNull(),
	title: varchar({ length: 50 }).notNull(),
	orderIndex: int("order_index").notNull(),
	incomeExpenseType: varchar("income_expense_type", { length: 50 }).notNull(),
	dateCreated: datetime("date_created", { mode: 'string', fsp: 6 }).notNull(),
	lastUpdated: datetime("last_updated", { mode: 'string', fsp: 6 }).notNull(),
	name: varchar({ length: 50 }).notNull(),
	// Warning: Can't parse bit(1) from database
	// bit(1)Type: bit(1)("analysis_included").notNull(),
	subtitle: varchar({ length: 50 }),
},
(table) => [
	primaryKey({ columns: [table.id], name: "income_expense_detail_base_type_id"}),
]);

export const incomeExpenseDetailType = mysqlTable("income_expense_detail_type", {
	id: varchar({ length: 255 }).notNull(),
	title: varchar({ length: 50 }).notNull(),
	orderIndex: int("order_index").notNull(),
	incomeExpenseType: varchar("income_expense_type", { length: 50 }).notNull(),
	dateCreated: datetime("date_created", { mode: 'string', fsp: 6 }).notNull(),
	lastUpdated: datetime("last_updated", { mode: 'string', fsp: 6 }).notNull(),
	name: varchar({ length: 50 }).notNull(),
	// Warning: Can't parse bit(1) from database
	// bit(1)Type: bit(1)("analysis_included").notNull(),
	accountGroupId: varchar("account_group_id", { length: 255 }).notNull().references(() => accountGroup.id, { onDelete: "restrict", onUpdate: "restrict" } ),
	subtitle: varchar({ length: 50 }),
},
(table) => [
	primaryKey({ columns: [table.id], name: "income_expense_detail_type_id"}),
]);

export const joinAccountGroupRequest = mysqlTable("join_account_group_request", {
	id: varchar({ length: 255 }).notNull(),
	senderId: varchar("sender_id", { length: 255 }).notNull().references(() => user.id, { onDelete: "restrict", onUpdate: "restrict" } ),
	receiverId: varchar("receiver_id", { length: 255 }).notNull().references(() => user.id, { onDelete: "restrict", onUpdate: "restrict" } ),
	lastUpdated: datetime("last_updated", { mode: 'string', fsp: 6 }).notNull(),
	ownerId: varchar("owner_id", { length: 255 }).notNull().references(() => user.id, { onDelete: "restrict", onUpdate: "restrict" } ),
	accountGroupId: varchar("account_group_id", { length: 255 }).notNull().references(() => accountGroup.id, { onDelete: "restrict", onUpdate: "restrict" } ),
	roles: varchar({ length: 255 }).notNull(),
},
(table) => [
	primaryKey({ columns: [table.id], name: "join_account_group_request_id"}),
]);

export const journey = mysqlTable("journey", {
	id: varchar({ length: 255 }).notNull(),
	startDate: datetime("start_date", { mode: 'string', fsp: 6 }).notNull(),
	regionId: varchar("region_id", { length: 255 }).notNull().references(() => region.id, { onDelete: "restrict", onUpdate: "restrict" } ),
	accountGroupId: varchar("account_group_id", { length: 255 }).notNull().references(() => accountGroup.id, { onDelete: "restrict", onUpdate: "restrict" } ),
	endDate: datetime("end_date", { mode: 'string', fsp: 6 }).notNull(),
},
(table) => [
	primaryKey({ columns: [table.id], name: "journey_id"}),
]);

export const journeyIncomeExpenseDetail = mysqlTable("journey_income_expense_detail", {
	id: varchar({ length: 255 }).notNull(),
	bankCardId: varchar("bank_card_id", { length: 255 }).references(() => bankCard.id, { onDelete: "restrict", onUpdate: "restrict" } ),
	dateCreated: datetime("date_created", { mode: 'string', fsp: 6 }).notNull(),
	paymentType: varchar("payment_type", { length: 255 }),
	lastUpdated: datetime("last_updated", { mode: 'string', fsp: 6 }).notNull(),
	location: varchar({ length: 255 }).notNull(),
	updatedBy: varchar("updated_by", { length: 255 }).notNull(),
	details: varchar({ length: 100 }).notNull(),
	currency: varchar({ length: 255 }),
	type: varchar({ length: 255 }).notNull(),
	accountGroupId: varchar("account_group_id", { length: 255 }).notNull().references(() => accountGroup.id, { onDelete: "restrict", onUpdate: "restrict" } ),
	createdBy: varchar("created_by", { length: 255 }).notNull(),
	incomeExpenseDate: datetime("income_expense_date", { mode: 'string', fsp: 6 }).notNull(),
	remark: varchar({ length: 100 }),
	journeyId: varchar("journey_id", { length: 255 }).notNull().references(() => journey.id, { onDelete: "restrict", onUpdate: "restrict" } ),
	amount: decimal({ precision: 19, scale: 2 }).notNull(),
	province: varchar({ length: 255 }),
	district: varchar({ length: 255 }),
	formattedAddress: varchar("formatted_address", { length: 255 }),
	latitude: decimal({ precision: 19, scale: 6 }),
	address: varchar({ length: 255 }),
	longitude: decimal({ precision: 19, scale: 6 }),
	city: varchar({ length: 255 }),
},
(table) => [
	index("idx_journey_income_expense_detail_income_expense_date").on(table.incomeExpenseDate),
	primaryKey({ columns: [table.id], name: "journey_income_expense_detail_id"}),
]);

export const region = mysqlTable("region", {
	id: varchar({ length: 255 }).notNull(),
	title: varchar({ length: 50 }).notNull(),
	name: varchar({ length: 50 }).notNull(),
},
(table) => [
	primaryKey({ columns: [table.id], name: "region_id"}),
]);

export const requestMap = mysqlTable("request_map", {
	id: varchar({ length: 255 }).notNull(),
	dateCreated: datetime("date_created", { mode: 'string', fsp: 6 }).notNull(),
	httpMethod: varchar("http_method", { length: 255 }),
	configAttribute: varchar("config_attribute", { length: 255 }).notNull(),
	lastUpdated: datetime("last_updated", { mode: 'string', fsp: 6 }).notNull(),
	url: varchar({ length: 255 }).notNull(),
},
(table) => [
	primaryKey({ columns: [table.id], name: "request_map_id"}),
	unique("UKd27576f6f7e718e71d707d8c47a1").on(table.httpMethod, table.url),
]);

export const role = mysqlTable("role", {
	id: varchar({ length: 255 }).notNull(),
	title: varchar({ length: 50 }).notNull(),
	dateCreated: datetime("date_created", { mode: 'string', fsp: 6 }).notNull(),
	authority: varchar({ length: 50 }).notNull(),
	description: varchar({ length: 50 }),
},
(table) => [
	primaryKey({ columns: [table.id], name: "role_id"}),
	unique("UK_rd7q9jehhtr53mwmnvso7qqyx").on(table.authority),
]);

export const user = mysqlTable("user", {
	id: varchar({ length: 255 }).notNull(),
	dateCreated: datetime("date_created", { mode: 'string', fsp: 6 }).notNull(),
	// Warning: Can't parse bit(1) from database
	// bit(1)Type: bit(1)("password_expired").notNull(),
	lastUpdated: datetime("last_updated", { mode: 'string', fsp: 6 }).notNull(),
	gender: int(),
	// Warning: Can't parse bit(1) from database
	// bit(1)Type: bit(1)("account_expired").notNull(),
	fullName: varchar("full_name", { length: 50 }).notNull(),
	authFailedAttempt: int("auth_failed_attempt").notNull(),
	username: varchar({ length: 50 }).notNull(),
	// Warning: Can't parse bit(1) from database
	// bit(1)Type: bit(1)("account_locked").notNull(),
	password: varchar({ length: 100 }).notNull(),
	accountGroupId: varchar("account_group_id", { length: 255 }).references(() => accountGroup.id, { onDelete: "restrict", onUpdate: "restrict" } ),
	// Warning: Can't parse bit(1) from database
	// bit(1)Type: bit(1)("enabled").notNull(),
	description: varchar({ length: 200 }),
	avatarFilename: varchar("avatar_filename", { length: 200 }),
},
(table) => [
	primaryKey({ columns: [table.id], name: "user_id"}),
	unique("UK_spx0dw50t0vogfi1b7lk98e2r").on(table.username),
]);

export const userActiveToken = mysqlTable("user_active_token", {
	id: varchar({ length: 255 }).notNull(),
	expiration: int().notNull(),
	dateCreated: datetime("date_created", { mode: 'string', fsp: 6 }).notNull(),
	dateGenerated: datetime("date_generated", { mode: 'string', fsp: 6 }).notNull(),
	username: varchar({ length: 50 }).notNull(),
	lastUpdated: datetime("last_updated", { mode: 'string', fsp: 6 }).notNull(),
	accessToken: varchar("access_token", { length: 3000 }).notNull(),
	refreshToken: varchar("refresh_token", { length: 3000 }).notNull(),
	dateExpired: datetime("date_expired", { mode: 'string', fsp: 6 }).notNull(),
	userAgent: varchar("user_agent", { length: 200 }),
	deviceId: varchar("device_id", { length: 200 }),
	// Warning: Can't parse bit(1) from database
	// bit(1)Type: bit(1)("enabled").notNull(),
	deviceSystemVersion: varchar("device_system_version", { length: 100 }),
	deviceProductName: varchar("device_product_name", { length: 100 }),
	deviceSystemName: varchar("device_system_name", { length: 100 }),
	deviceModel: varchar("device_model", { length: 100 }),
	deviceBrand: varchar("device_brand", { length: 100 }),
	appVersion: varchar("app_version", { length: 100 }),
	operatingSystemVersion: varchar("operating_system_version", { length: 100 }),
	platformVersion: varchar("platform_version", { length: 100 }),
	operatingSystem: varchar("operating_system", { length: 100 }),
	ipAddress: varchar("ip_address", { length: 255 }).notNull(),
	accountGroupId: varchar("account_group_id", { length: 255 }).notNull(),
},
(table) => [
	primaryKey({ columns: [table.id], name: "user_active_token_id"}),
]);

export const userRole = mysqlTable("user_role", {
	id: varchar({ length: 255 }).notNull(),
	roleId: varchar("role_id", { length: 255 }).notNull().references(() => role.id, { onDelete: "restrict", onUpdate: "restrict" } ),
	userId: varchar("user_id", { length: 255 }).notNull().references(() => user.id, { onDelete: "restrict", onUpdate: "restrict" } ),
	accountGroupId: varchar("account_group_id", { length: 255 }).notNull().references(() => accountGroup.id, { onDelete: "restrict", onUpdate: "restrict" } ),
},
(table) => [
	primaryKey({ columns: [table.id], name: "user_role_id"}),
	unique("UK49ad9a5625dc55a282f234802c2b").on(table.userId, table.accountGroupId, table.roleId),
]);
